﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Web;
using Yule8xCommon.Applib;

namespace Yule8xCommon.Db
{
    public class DbFactory
    {
        public string ProviderName { get; private set; }
        public string ConnectionString { get; private set; }

        public T FindEntity<T>(string sqlstmt)
        {
            return default(T);
        }

        public int Timeout { get; private set; }
        public DbFactory(string providerName, string connectionString, int timeout = 30)
        {
            this.ProviderName = providerName;
            this.ConnectionString = connectionString;
            this.Timeout = timeout;
        }

        public static ConnectionStringSettings GetConnectionStringSettings(string name)
        {
            ConnectionStringSettings connConf = ConfigurationManager.ConnectionStrings[name];
            if (null == connConf)
            {
                throw new Exception(string.Format("ConnectionStringSetting \"{0}\" is not define.", name));
            }
            return connConf;

        }
        // 2017-4-22
        public static DbParams CreateParams(string providerName = null)
        {

            if (string.IsNullOrEmpty(providerName) && null != Instance)
            {
                providerName = Instance.ProviderName;
            }
            return string.IsNullOrEmpty(providerName) ? null : new DbParams(providerName);
        }
        public static DbFactory Instance;

        public static string GetInsertStatement(string table, Dictionary<string, string> data)
        {
            return string.Format("insert into {0} ([{1}]) values ('{2}')", table, string.Join("],[", data.Keys), string.Join("','", data.Values));
        }
        public static string GetUpdateStatement(string table, string where, Dictionary<string, string> data)
        {
            if (data.Count < 1 || string.IsNullOrEmpty(table))
            {
                return string.Empty;
            }
            string sqlstmt = "";
            foreach (string field in data.Keys)
            {
                sqlstmt += string.Format(",[{0}]='{1}'", field, data[field]);
            }

            where = string.IsNullOrEmpty(where) ? string.Empty : " where " + where;
            return string.Format("update {0} set {1}{2}", table, sqlstmt.Substring(1), where);

        }


        private static Dictionary<string, DbFactory> instances;
        public static DbFactory Init(string name, bool changeInstance = false)
        {
            ConnectionStringSettings connConf = GetConnectionStringSettings(name);
            return Init(name, connConf.ProviderName, connConf.ConnectionString, changeInstance);
        }
        public static DbFactory Init(string id, string providerName, string connectionString, bool changeInstance = false)
        {
            if (null == instances)
            {
                instances = new Dictionary<string, Db.DbFactory>();
            }
            DbFactory instance;
            if (instances.Keys.Contains(id))
            {
                instance = instances[id];
                if (changeInstance)
                {
                    Instance = instance;
                }
                return instance;
            }

            instance = new DbFactory(providerName, connectionString);
            instances.Add(id, instance);
            if (changeInstance || null == Instance)
            {
                Instance = instance;
            }
            return instance;
        }

        public static void TestConnection(object conn_bozhong)
        {
            throw new NotImplementedException();
        }

        /// <summary>
        /// 获取通用数据连接工厂对象
        /// </summary>
        /// <param name="providerName"></param>
        /// <returns></returns>
        public DbProviderFactory GetDbProviderFactory()
        {
            return DbProviderFactories.GetFactory(this.ProviderName);
        }
        public DbConnection GetDbConnection()
        {
            DbProviderFactory dbpf = DbProviderFactories.GetFactory(this.ProviderName);
            DbConnection conn = dbpf.CreateConnection();
            conn.ConnectionString = this.ConnectionString;
            return conn;
        }
        public bool TestConnection(out string message)
        {
            bool result = false;
            using (DbConnection conn = GetDbConnection())
            {
                message = string.Format("{0}:{1}", this.ProviderName, this.ConnectionString);
                try
                {
                    conn.Open();
                    result = true;
                }
                catch (Exception ex)
                {
                    message = ex.Message;
                }
            }
            return result;
        }
        /// <summary>
        /// 执行一条命令并返回受影响的行数
        /// </summary>
        /// <param name="sqlstmt"></param>
        /// <param name="paras">参数列表</param>
        /// <returns></returns>
        public int ExecuteNonQuery(string sqlstmt, DbParameter[] paras = null)
        {
            if (string.IsNullOrEmpty(sqlstmt)) return 0;
            int intOut = 0;
            using (DbConnection conn = GetDbConnection())
            {
                using (DbCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sqlstmt;
                    if (Timeout > 0) cmd.CommandTimeout = Timeout;
                    if (paras != null) cmd.Parameters.AddRange(paras);
                    conn.Open();
                    intOut = cmd.ExecuteNonQuery();
                }
            }
            return intOut;
        }

        /// <summary>
        /// 执行命令并返回一个数据表
        /// <para>无记录则返回new DataTable()</para>
        /// </summary>
        /// <param name="sqlstmt"></param>
        /// <param name="paras">参数列表</param>
        /// <returns></returns>
        public DataTable ExecuteDataTable(string sqlstmt, DbParameter[] paras = null)
        {
            DataTable dt = new DataTable();
            if (string.IsNullOrEmpty(sqlstmt)) return dt;
            try
            {
                using (DbConnection conn = GetDbConnection())
                {
                    using (DbCommand cmd = conn.CreateCommand())
                    {
                        cmd.CommandText = sqlstmt;
                        if (Timeout > 0) cmd.CommandTimeout = Timeout;
                        if (paras != null) cmd.Parameters.AddRange(paras);
                        DbDataAdapter dbda = GetDbProviderFactory().CreateDataAdapter();
                        dbda.SelectCommand = cmd;
                        conn.Open();
                        dbda.Fill(dt);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }

        #region 存储过程操纵方法

        /// <summary>
        /// 执行存储过程,返回新的参数值集合
        /// </summary>
        /// <param name="procedureName"></param>
        /// <param name="dbParameters"></param>
        /// <returns></returns>
        public DbParameter[] ExecuteProcedureNonQuery(string procedureName, params DbParameter[] dbParameters)
        {
            int iOut = 0;
            using (DbConnection conn = GetDbConnection())
            {
                DbCommand oCmd = conn.CreateCommand();
                oCmd.Parameters.AddRange(dbParameters);
                oCmd.CommandType = CommandType.StoredProcedure;
                oCmd.CommandText = procedureName;
                conn.Open();
                iOut = oCmd.ExecuteNonQuery();
            }
            return dbParameters;
        }

        /// <summary>
        /// 执行存储过程并返回DataTable对象
        /// </summary>
        /// <param name="procedureName"></param>
        /// <param name="outParameters"></param>
        /// <param name="dbParameters"></param>
        /// <returns></returns>
        public DataTable ExecuteProcedureDataTable(string procedureName, ref Dictionary<string, object> outParameters, params DbParameter[] dbParameters)
        {
            DataTable oDt = new DataTable();
            using (DbConnection conn = GetDbConnection())
            {
                DbCommand oCmd = conn.CreateCommand();
                oCmd.CommandText = procedureName;
                oCmd.CommandType = CommandType.StoredProcedure;
                oCmd.Parameters.AddRange(dbParameters);
                DbDataAdapter oDa = GetDbProviderFactory().CreateDataAdapter();
                oDa.SelectCommand = oCmd;
                conn.Open();
                oDa.Fill(oDt);
            }
            if (outParameters == null) return oDt;
            foreach (DbParameter para in dbParameters)
            {
                outParameters[para.ParameterName] = para.Value;
            }
            return oDt;
        }

        /// <summary>
        /// 执行存储过程并返回DataTable对象
        /// </summary>
        /// <param name="procedureName"></param>
        /// <param name="dbParameters"></param>
        /// <returns></returns>
        public DataTable ExecuteProcedureDataTable(string procedureName, params DbParameter[] dbParameters)
        {
            Dictionary<string, object> dict = null;
            return this.ExecuteProcedureDataTable(procedureName, ref dict, dbParameters);
        }

        #endregion

        #region 事务操作

        /// <summary>
        /// 执行一条命令并返回受影响的行数
        /// </summary>
        /// <param name="sqlstmt"></param>
        /// <param name="paras">参数列表</param>
        /// <returns></returns>
        public int ExecuteNonQueryTrans(string sqlstmt, DbParameter[] paras = null)
        {
            if (string.IsNullOrEmpty(sqlstmt)) return 0;
            int intOut = 0;
            using (DbConnection conn = GetDbConnection())
            {
                using (DbCommand cmd = conn.CreateCommand())
                {
                    conn.Open();
                    DbTransaction trans = conn.BeginTransaction();
                    cmd.CommandText = sqlstmt;
                    if (paras != null) cmd.Parameters.AddRange(paras);
                    cmd.Transaction = trans;
                    try
                    {
                        intOut = cmd.ExecuteNonQuery();
                        trans.Commit();
                    }
                    catch (Exception)
                    {
                        trans.Rollback();
                    }
                    finally
                    {
                        trans.Dispose();
                    }
                }
            }
            return intOut;
        }
        #endregion

        public int ExecuteCount(string stmt)
        {
            return Convert.ToInt32(this.GetValue(stmt));
        }
        public T ExecuteSum<T>(string sqlstmt, T defv = default(T))
        {
            object value = this.GetValue(sqlstmt);
            if (null == value)
            {
                return defv;
            }
            try
            {
                return (T)Convert.ChangeType(value, typeof(T));
            }
            catch
            {
                return defv;
            }
        }
        public string ExecuteSum(string sqlstmt, string defv = "0")
        {
            object value = this.GetValue(sqlstmt);
            return (null == value) ? defv : value.ToString();
        }

        /// <summary>
        /// 获取值
        /// <para>无记录默认返回null</para>
        /// </summary>
        /// <param name="stmt"></param>
        /// <param name="defv"></param>
        /// <returns></returns>
        public object GetValue(string stmt, object defv = null)
        {
            using (DbCommand cmd = this.GetDbConnection().CreateCommand())
            {
                cmd.CommandText = stmt;
                if (Timeout > 0) cmd.CommandTimeout = Timeout;
                cmd.Connection.Open();
                using (IDataReader dr = cmd.ExecuteReader())
                {
                    object ovalue;
                    while (dr.Read())
                    {
                        ovalue = dr[0];
                        return DBNull.Value == ovalue ? defv : ovalue;
                    }
                }
                cmd.Connection.Close();
                cmd.Connection.Dispose();
            }
            return defv;
        }
    }
}